﻿/*
 * Created by SharpDevelop.
 * User: CeCi
 * Date: 25/04/2014
 * Time: 06:51 p.m.
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using Gestión_UTN.Entities;

namespace Gestión_UTN.daimpl
{
    /// <summary>
    /// Description of notadaimpl.
    /// </summary>
    public class notadaimpl : da.notada
    {
        public notadaimpl()
        {
        }

        public bool SaveNota(int alu_dni, int fe_id, string valor)
        {
            bool ingresada = false;

            OleDbConnection conex = null;
            OleDbTransaction trans = null;

            try
            {
                conex = util.Conexion.conectarAccess();
                trans = null;

                if (null != conex)
                {

                    string sql = "UPDATE ine_inscripcionExamenes SET ine_nota = @nota " +
                                    "WHERE alu_dni = @alu_dni AND fe_id = @fe_id";

                    conex.Open();
                    trans = conex.BeginTransaction();

                    OleDbCommand insertar = new OleDbCommand(sql, conex, trans);

                    insertar.Parameters.AddWithValue("@nota", valor);
                    insertar.Parameters.AddWithValue("@alu_dni", alu_dni);
                    insertar.Parameters.AddWithValue("@fe_id", fe_id);

                    insertar.ExecuteNonQuery();

                    trans.Commit();

                    ingresada = true;

                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                ingresada = false;
                trans.Rollback();
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return ingresada;
        }

        public bool DeleteNota(int alu_dni, int fe_id)
        {
            bool eliminada = false;

            OleDbConnection conex = null;
            OleDbTransaction trans = null;

            try
            {
                conex = util.Conexion.conectarAccess();
                trans = null;

                if (null != conex)
                {

                    string sql = "UPDATE ine_inscripcionExamenes SET ine_nota = '' " +
                                    "WHERE alu_dni = @alu_dni AND fe_id = @fe_id";

                    conex.Open();

                    trans = conex.BeginTransaction();

                    OleDbCommand eliminar = new OleDbCommand(sql, conex, trans);

                    eliminar.Parameters.AddWithValue("@alu_dni", alu_dni);
                    eliminar.Parameters.AddWithValue("@fe_id", fe_id);

                    eliminar.ExecuteNonQuery();

                    trans.Commit();

                    eliminada = true;

                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                eliminada = false;
                trans.Rollback();
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return eliminada;
        }

        public bool ModifyNota(int alu_dni, int fe_id, string valor)
        {
            bool modificada = false;

            OleDbConnection conex = null;
            OleDbTransaction trans = null;

            try
            {
                conex = util.Conexion.conectarAccess();
                trans = null;

                if (null != conex)
                {

                    string sql = "UPDATE ine_inscripcionExamenes SET ine_nota = @valor " +
                                    "WHERE alu_dni = @alu_dni AND fe_id = @fe_id";

                    conex.Open();
                    trans = conex.BeginTransaction();

                    OleDbCommand modificar = new OleDbCommand(sql, conex, trans);

                    modificar.Parameters.AddWithValue("@valor", valor);
                    modificar.Parameters.AddWithValue("@alu_dni", alu_dni);
                    modificar.Parameters.AddWithValue("@fe_id", fe_id);

                    modificar.ExecuteNonQuery();

                    trans.Commit();

                    modificada = true;

                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                modificada = false;
                trans.Rollback();
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return modificada;
        }

        public System.Data.DataTable FindAllNotas()
        {
            DataTable tablaNotas = null;

            OleDbConnection conex = null;

            try
            {
                conex = util.Conexion.conectarAccess();

                tablaNotas = new DataTable();

                if (null != conex)
                {

                    string sql = "SELECT alu.alu_dni, alu_nombre, alu_apellido, tie_tipo, mat_nombre, car_nombre, fe.fe_id, fe_fecha, ine_nota" +
                                    " FROM ine_inscripcionExamenes ine, alu_alumnos alu, fe_fechasExamenes fe, tie_tipoExamen tie, " +
                                    " mat_materias mat, car_carreras car " +
                                    "WHERE ine.alu_dni = alu.alu_dni AND ine.fe_id = fe.fe_id AND fe.tie_id = tie.tie_id AND fe.mat_id = mat.mat_id" +
                                    " AND mat.car_id = car.car_id";

                    OleDbDataAdapter adaptador = new OleDbDataAdapter(sql, conex);

                    conex.Open();

                    adaptador.Fill(tablaNotas);

                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                tablaNotas = null;
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return tablaNotas;
        }

        public System.Collections.Generic.List<Gestión_UTN.Entities.Nota> FindNotaByDni(int alu_dni)
        {
            List<Nota> notas = null;

            OleDbConnection conex = null;

            try
            {
                conex = util.Conexion.conectarAccess();

                if (null != conex)
                {

                    notas = new List<Nota>();

                    string sql = "SELECT alu_dni, fe_id, ine_nota " +
                                    "FROM ine_inscripcionesExamenes ine " +
                                    "WHERE alu_dni = @dni";

                    OleDbCommand consulta = new OleDbCommand(sql, conex);

                    conex.Open();

                    consulta.Parameters.AddWithValue("@dni", alu_dni);

                    OleDbDataReader lector = consulta.ExecuteReader();

                    while (lector.Read())
                    {
                        int fe_id = Convert.ToInt32(lector[1].ToString());
                        string nota = lector[2].ToString();

                        Nota not = new Nota(alu_dni, fe_id, nota);

                        notas.Add(not);

                        not = null;
                    }


                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                notas = null;
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return notas;
        }

        public System.Collections.Generic.List<Gestión_UTN.Entities.Nota> FindNotaByFecha(int fe_id)
        {
            List<Nota> notas = null;

            OleDbConnection conex = null;

            try
            {
                conex = util.Conexion.conectarAccess();

                if (null != conex)
                {

                    notas = new List<Nota>();

                    string sql = "SELECT alu_dni, fe_id, ine_nota " +
                                    "FROM ine_inscripcionesExamenes ine " +
                                    "WHERE fe_id = @fe_id";

                    OleDbCommand consulta = new OleDbCommand(sql, conex);

                    conex.Open();

                    consulta.Parameters.AddWithValue("@fe_id", fe_id);

                    OleDbDataReader lector = consulta.ExecuteReader();

                    while (lector.Read())
                    {
                        int dni = Convert.ToInt32(lector[0].ToString());
                        string nota = lector[3].ToString();

                        Nota not = new Nota(dni, fe_id, nota);
                        notas.Add(not);

                        not = null;
                    }

                    conex.Close();
                }

            }
            catch (Exception ex)
            {
                notas = null;
                conex.Close();

                throw new exceptions.daexception(ex.Message);
            }

            return notas;
        }
    }
}
